package com.qf.dao.impl;

import com.qf.dao.BookDao;
import com.qf.entity.Book;
import com.qf.entity.BookVo;
import com.qf.utils.DBUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl implements BookDao {
    @Override
    public List<Book> selectPage(BookVo bookVo, Integer currentPageNum, Integer pageSize) throws SQLException {
        StringBuilder sb = new StringBuilder("select b.*,c.cname cname from book b inner join category c on b.cid = c.cid where 1=1 ");
        List<Object> paramList = new ArrayList<>();
        handleParam(bookVo, sb, paramList);

        sb.append(" limit ?, ? ");
        paramList.add((currentPageNum - 1) * pageSize);
        paramList.add(pageSize);

        return DBUtils.DB.qr.query(sb.toString(), new BeanListHandler<>(Book.class), paramList.toArray());
    }

    @Override
    public Integer getTotalCount(BookVo bookVo) throws SQLException {
        StringBuilder sb = new StringBuilder("select count(1) from book b where 1=1 ");
        List<Object> paramList = new ArrayList<>();
        handleParam(bookVo, sb, paramList);

        Long count = (Long) DBUtils.DB.qr.query(sb.toString(), new ScalarHandler(), paramList.toArray());
        return count == null ? 0 : Integer.parseInt(count + "");
    }

    @Override
    public int add(Book book) throws SQLException {
        String sql = "INSERT INTO book(cid, bname, author,price, description, version,state, book_date) VALUE (?, ?, ?, ?, ?, ?, ?, ?)";
        Integer cid = book.getCid();
        String bname = book.getBname();
        String author = book.getAuthor();
        Double price = book.getPrice();
        String description = book.getDescription();
        String version = book.getVersion();
        Integer state = book.getState();
        LocalDateTime BookDate = book.getBook_date();
        Object[] objects = {cid, bname, author, price, description, version, state, BookDate};
        return DBUtils.DB.qr.update(sql, objects);
    }

    @Override
    public int update(Book book) throws SQLException {
        String sql = "UPDATE book SET cid = ?, bname = ?, author = ?, price = ?, description = ?,  version = ?,state = ?, book_date = ? " +
                "WHERE bid = ?";
        Integer cid = book.getCid();
        String bname = book.getBname();
        String author = book.getAuthor();
        Double price = book.getPrice();
        String description = book.getDescription();
        String version = book.getVersion();
        Integer state = book.getState();
        Integer bid = book.getBid();
        LocalDateTime bookDate = book.getBook_date();
        Object[] objects = {cid, bname, author, price, description, version, state, bookDate, bid};
        return DBUtils.DB.qr.update(sql, objects);
    }

    @Override
    public int delete(String ids) throws SQLException {
        String sql = "DELETE FROM book WHERE bid IN (" + ids + ")";
        return DBUtils.DB.qr.update(sql);
    }

    @Override
    public Book selectBook(Integer bid) throws SQLException {
        String sql = "SELECT b.*, c.cname cname FROM book b INNER JOIN category c ON b.cid = c.cid WHERE bid = ?";
        return DBUtils.DB.qr.query(sql, new BeanHandler<>(Book.class), bid);
    }

    private void handleParam(BookVo bookVo, StringBuilder sb, List<Object> paramList) {
        String bname = bookVo.getBname();
        String state = bookVo.getState();
        String startTime = bookVo.getStartTime();
        String endTime = bookVo.getEndTime();

        if (bname != null && !"".equals(bname)) {
            sb.append(" and b.bname like ? ");
            paramList.add("%" + bname + "%");
        }
        if (state != null && !"".equals(state)) {
            sb.append(" and b.state = ? ");
            paramList.add(state);
        }
        if (startTime != null && !"".equals(startTime)) {
            sb.append(" and book_date > ? ");
            paramList.add(startTime);
        }
        if (endTime != null && !"".equals(endTime)) {
            sb.append(" and book_date < ? ");
            paramList.add(endTime);
        }
    }
}
